﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Linq;

namespace DataCoreTechnologies.SqlServer
{
   /// <summary>
   /// Contains information about a table belonging to a schema.
   /// </summary>
   public sealed class Table : DatabaseObject
   {
      #region Public
      //Properties
      /// <summary>
      /// The approximate number of records contained in this table.
      /// </summary>
      public Int64 RowCount { get; internal set; }
      /// <summary>
      /// The approximate amount of disk space this table's data uses.
      /// </summary>
      public Int64 DataSpaceUsed { get; internal set; }
      /// <summary>
      /// The approximate amount of disk space this table's indexes use.
      /// </summary>
      public Int64 IndexSpaceUsed { get; internal set; }
      /// <summary>
      /// The approximate amount of disk space available.
      /// </summary>
      public Int64 DataSpaceAvailable { get; internal set; }
      /// <summary>
      /// The approximate amount of total disk space this table uses.
      /// </summary>
      public Int64 TableSize { get { return DataSpaceUsed + IndexSpaceUsed + DataSpaceAvailable; } }
      /// <summary>
      /// The primary key belonging to this table.
      /// </summary>
      public PrimaryKey PrimaryKey { get; internal set; }
      /// <summary>
      /// Returns a T-Sql create statement for this table.
      /// </summary>
      public string DDL
      {
         get
         {
             if (_ddl == "")
             {
                 var ddlString = @"CREATE TABLE [" + Schema.Name + @"].[" + this.Name + @"](" + "\r\n";
                 foreach (Column column in _columns)
                 {
                     //Column name
                     ddlString += "    [" + column.Name + @"] [";
                     //Column type and size
                     if (column.UserType != null)
                         ddlString += column.UserType.Schema.Name + "].[" + column.UserType.Name + @"]";
                     else
                     {
                         ddlString += column.SystemType.Name + @"]";
                         if (column.SystemType.Category == SystemTypeCategory.CharacterStrings)
                             ddlString += "(" + column.MaxLength.ToString() + ")";
                         else if (column.SystemType.Category == SystemTypeCategory.UnicodeCharacterStrings)
                             ddlString += "(" + (column.MaxLength / 2).ToString() + ")";
                         else if (column.SystemType.Category == SystemTypeCategory.ExactNumerics)
                             ddlString += "(" + column.Precision.ToString() + ", " + column.Scale.ToString() + ")";
                     }
                     
                     //Is identity
                     if (column.IsPrimaryKey)
                         ddlString += " IDENTITY(1,1)";
                     //Is nullable
                     if (column.IsNullable)
                         ddlString += " NULL";
                     else
                         ddlString += " NOT NULL";
                     //Has default
                     if (column.DefaultValue != null)
                         ddlString += @" CONSTRAINT [" + column.DefaultValue.Name +
                             "] DEFAULT (" + column.DefaultValue.Formula + @")";
                     //Add a final column
                     ddlString += ",\r\n";
                 }
                 //Has primary key
                 if (PrimaryKey != null)
                 {
                     ddlString += "    CONSTRAINT [" + PrimaryKey.Name + "] PRIMARY KEY CLUSTERED\r\n";
                     ddlString += "    (\r\n";
                     foreach (IndexColumn indexColumn in PrimaryKey.Index.IndexColumns)
                     {
                         ddlString += "        [" + indexColumn.Column.Name + "]";
                         if (!indexColumn.IsDescending) ddlString += " ASC";
                         ddlString += ",\r\n";
                     }
                     ddlString = ddlString.Substring(0, ddlString.Length - 3) + "\r\n";
                     ddlString += "    ),\r\n";
                 }

                 ddlString = ddlString.Substring(0, ddlString.Length - 3) + "\r\n";
                 ddlString += @") ON [PRIMARY]" + "\r\n";

                 ddlString += "GO\r\n";

                 //Foreign keys
                 foreach (ForeignKey fk in this.ForeignKeys)
                 {
                     ddlString += "ALTER TABLE [" + fk.Table.Schema.Name + "].["
                         + fk.Table.Name + "] WITH CHECK ADD CONSTRAINT ["
                         + fk.Name + "]\r\nFOREIGN KEY(";

                     string referenceSchema = "";
                     string referenceTable = "";
                     foreach (ForeignKeyColumns fkc in fk.ForeignKeyColumns)
                     {
                         ddlString += "[" + fkc.ParentColumn.Name + "], ";
                         referenceSchema = fkc.ReferenceColumn.Table.Schema.Name;
                         referenceTable = fkc.ReferenceColumn.Table.Name;
                     }
                     ddlString = ddlString.Substring(0, ddlString.Length - 2);

                     ddlString += ")\r\nREFERENCES [" + referenceSchema + "].["
                         + referenceTable + "] (";

                     foreach (ForeignKeyColumns fkc in fk.ForeignKeyColumns)
                     {
                         ddlString += "[" + fkc.ReferenceColumn.Name + "], ";
                     }
                     ddlString = ddlString.Substring(0, ddlString.Length - 2);

                     ddlString += ")\r\n";
                     ddlString += "GO\r\n";

                     ddlString += "ALTER TABLE [" + fk.Table.Schema.Name + "].["
                         + fk.Table.Name + "]\r\n";
                     ddlString += "CHECK CONSTRAINT [" + fk.Name + "]\r\n";
                     ddlString += "GO\r\n";
                 }

                 //Check constraints
                 var checkConstraints = (from column in this.Columns
                                         where column.CheckConstraint != null
                                         select column.CheckConstraint);
                 foreach (CheckConstraint checkConstraint in checkConstraints)
                 {
                     ddlString += "ALTER TABLE [" + checkConstraint.Column.Table.Schema.Name + "].["
                         + checkConstraint.Column.Table.Name + "] WITH CHECK ADD CONSTRAINT ["
                         + checkConstraint.Name + "] CHECK (" + checkConstraint.Formula + ")\r\n";
                     ddlString += "GO\r\n";
                     ddlString += "ALTER TABLE [" + checkConstraint.Column.Table.Schema.Name + "].["
                         + checkConstraint.Column.Table.Name + "] CHECK CONSTRAINT ["
                         + checkConstraint.Name + "]\r\n";
                     ddlString += "GO\r\n";
                 }

                 _ddl = ddlString;
             }
             return _ddl;
         }
      }
      /// <summary>
      /// A collection of columns belonging to this table.
      /// </summary>
      public IEnumerable<Column> Columns
      {
         get
         {
             for (int index = 0; index < _columns.Count; index++)
             {
                 yield return _columns[index];
             }
         }
      }
      /// <summary>
      /// A collection of indexes belonging to this table.
      /// </summary>
      public IEnumerable<Index> Indexes
      {
         get
         {
             for (int index = 0; index < _indexes.Count; index++)
             {
                 yield return _indexes[index];
             }
         }
      }
      /// <summary>
      /// A collection of triggers belonging to this table.
      /// </summary>
      public IEnumerable<Trigger> Triggers
      {
         get
         {
             for (int index = 0; index < _triggers.Count; index++)
             {
                 yield return _triggers[index];
             }
         }
      }
      /// <summary>
      /// A collection of foreign keys belonging to this table.
      /// </summary>
      public IEnumerable<ForeignKey> ForeignKeys
      {
         get
         {
             for (int index = 0; index < _foreignKeys.Count; index++)
             {
                 yield return _foreignKeys[index];
             }
         }
      }
      /// <summary>
      /// A collection of extended properties belonging to this table.
      /// </summary>
      public IEnumerable<ExtendedProperty> ExtendedProperties
      {
         get
         {
             for (int index = 0; index < _extendedProperties.Count; index++)
             {
                 yield return _extendedProperties[index];
             }
         }
      }
      //Methods
      /// <summary>
      /// Returns a list of T-Sql statements that compromise the create statement for this table.
      /// </summary>
      /// <returns></returns>
      public List<string> GetDDLTokens() { return TSql.GetDDLTokens(DDL); }
      #endregion Public
      #region Internal
      //Constructor
      /// <summary>
      /// Creates a table and associates it to the schema specified by the parameter.
      /// </summary>
      /// <param name="container">The schema this table belongs to.</param>
      internal Table(Schema container)
      {
         Schema = container;
         InstanceId = System.Guid.NewGuid();
         PrimaryKey = null;
      }
      //Methods
      /// <summary>
      /// Associates a column with this table.
      /// </summary>
      /// <param name="column">The column that belongs to this table.</param>
      internal void AddColumn(Column column) { _columns.Add(column); }
      /// <summary>
      /// Associates a index with this table.
      /// </summary>
      /// <param name="index">The index that belongs to this table.</param>
      internal void AddIndex(Index index) { _indexes.Add(index); }
      /// <summary>
      /// Associates a trigger with this table.
      /// </summary>
      /// <param name="trigger">The trigger that belongs to this table.</param>
      internal void AddTrigger(Trigger trigger) { _triggers.Add(trigger); }
      /// <summary>
      /// Associates a foreign key with this table.
      /// </summary>
      /// <param name="foreignKey">The foreign key that belongs to this table.</param>
      internal void AddForeignKey(ForeignKey foreignKey) { _foreignKeys.Add(foreignKey); }
      /// <summary>
      /// Associates a extended property with this table.
      /// </summary>
      /// <param name="extendedProperty">The extended property that belongs to this table.</param>
      internal void AddExtendedProperty(ExtendedProperty extendedProperty) { _extendedProperties.Add(extendedProperty); }
      #endregion Internal
      #region Private
      //Properties
      private string _ddl = "";
      private List<Column> _columns = new List<Column>();
      private List<Index> _indexes = new List<Index>();
      private List<Trigger> _triggers = new List<Trigger>();
      private List<ForeignKey> _foreignKeys = new List<ForeignKey>();
      private List<ExtendedProperty> _extendedProperties = new List<ExtendedProperty>();
      #endregion
   }
}
